package henu.dao.impl;

import henu.bean.Permission;
import henu.bean.Role;
import henu.bean.RolePermission;
import henu.dao.PermissionDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


/**
 * 权限管理
 * 15-03-20
 * @author wuhaifeng 
 * */
public class PermissionDaoImpl implements PermissionDao{

	// 找到所有权限
	public List<Permission> findAllPermission() {
		List<Permission> list = new ArrayList<Permission>();
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from PERMISSION";
		try {
			list = runner.query(sql, new BeanListHandler<Permission>(
					Permission.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 找到所有角色
	public List<Role> findAllRole() {
		List<Role> list = new ArrayList<Role>();
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from ROLE";
		try {
			list = runner.query(sql, new BeanListHandler<Role>(Role.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 找到参照表里的所有数据
	public List<RolePermission> findAllRP() {
		List<RolePermission> list = new ArrayList<RolePermission>();
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from ROLE_PER";
		try {
			list = runner.query(sql, new BeanListHandler<RolePermission>(
					RolePermission.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<RolePermission> findRPByProperty(String property, String key,
			String order, String sort) {
		List<RolePermission> list = new ArrayList<RolePermission>();
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from ROLE_PER where  " + property + "= " + key
				+ " order by " + order + " " + sort;
		//System.out.println("执行的sql是："+sql);
		try {
			list = runner.query(sql, new BeanListHandler<RolePermission>(
					RolePermission.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据权限的id找到这个权限
	 * 
	 * @param pid
	 *            权限的id
	 * */
	public Permission findPermisstionById(int pid) {
		Permission p = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from Permission where  pid  = ?" ;
		try {
			p = runner.query(sql, new BeanHandler<Permission>(Permission.class),pid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}

	/**
	 * 根据指定的属性找到对应的权限记录
	 * 
	 * @param pid
	 *            权限的id
	 * */
	
/*	public static void main(String[] args) {
		PermissionDaoImpl dao = new PermissionDaoImpl();
		
		int x = dao.findRPByProperty("roleid", "21", "pid", "asc").size();
		System.out.println(x);
	}*/
	public List<Permission> findPermissionByProperty(String property,
			String key, String order, String sort) {
		List<Permission> list = new ArrayList<Permission>();
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from permission where  " + property + "= ?"
				+ " order by " + order + " " + sort;
		try {
			list = runner.query(sql, new BeanListHandler<Permission>(
					Permission.class), key);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 插入角色的一条记录
	 * 
	 * @param role
	 *            一个新的角色
	 * */
	public int saveRole(Role role) {
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		// INSERT INTO test VALUES(2,1,1,2);
		String sql = "INSERT into role VALUES(?,?,?,?,?)";
		Object[] params = { null, role.getRolename(), role.getStatus(),
				role.getDesc(), role.getRemark() };
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}

	/**
	 * 删除一个角色
	 * 
	 * @param id
	 *            要删除的角色的id
	 * */
	public int delRole(String id) {
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		String sql = "delete from role where roleid = ?";
		try {
			result = runner.update(sql, id);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}

	/**
	 * @param pid
	 *            权限的id
	 * @param rid
	 *            角色的id
	 * @return 返回操作是否成功
	 * */
	public int addPermission(RolePermission rp) {
		int result = 0;
		QueryRunner runner = DaoFactory.getRunner();
		// INSERT INTO test VALUES(2,1,1,2);
		String sql = "INSERT INTO ROLE_PER VALUES(?,?,?,?);";
		Object[] params = { null, rp.getRoleId(), rp.getPid(),
				rp.getDescription() };
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}

	/**
	 * 添加整个模块的权限的方法
	 * 
	 * @param modelname
	 *            模块名称
	 * @param roleid
	 *            角色的名称
	 * */

	public int addModelPermission(String modelname, int roleid) {
		// 此处要进行事务操作所以是使用Connection
		Connection conn = null;
		int result = 0;
		try {
			conn = Dbcp.getConnection();
			conn.setAutoCommit(false);
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm");
			List<Permission> modelper = findPermissionByProperty("PNAME",modelname, "pid", "asc");
			//System.out.println("模块"+modelname+"的权限数量：" + modelper.size());
			// 首先找到这个模块的所有的连接
			QueryRunner runner = new QueryRunner();
			// 现在要根据pname查到所有的id
			// 已经找到一个模块对应的所有的权限记录
			Date d = new Date();
			String sql = "INSERT INTO ROLE_PER VALUES(?,?,?,'"+df.format(d)+"')";
			// 循环添加权限
			Object[] params = { null, roleid, ""}; // 最后是插入时候的描述,改成修改这个权限的时间把
			for (Permission p : modelper) {
				params[2] = p.getPid(); // 权限的id
				// 执行sql
				result = runner.update(conn,sql, params);
				//System.out.println("插入权限执行结果：" + result); // 只有全部为1 才能判定是权限植入成功
				if (result < 1) {
						conn.rollback(); // 只要有一个操作不成直接回滚事务，并结束程序
						if(conn!=null&&conn.isClosed() == false){
							conn.close();
						}
					return 0; 			// 结束程序并返回执行结果为不成功0
				}
			}

			conn.commit(); // 以上所有代码执行完毕没有提示失败就是成功了 可以直接返回1 并且提交事物
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {// 提交事物之后还要关闭所有的连接
			try {
				if (conn!=null&&conn.isClosed() == false) {
					conn.close(); // 假如连接没有关闭的话就把连接关闭了
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public Role findRoleById(String roleid) {
		Role role = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * from Role where  roleid  = ?" ;
		try {
			role = runner.query(sql, new BeanHandler<Role>(Role.class),roleid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return role;
	}



	public int delRPByRoleid(String roleid) {
		QueryRunner runner = new QueryRunner();	//假如有一条操作失败就回滚事物
		Connection conn = null ;
		int result = 0;
		//第一步查找所这个人的权限总数
		//第二部找到删除的总条数
		//假如不一致就事物回滚
		String sql_count ="SELECT count(*) from ROLE_PER where ROLEID =?";				//查询到一个人的所有的权限记录数量
		String sql_del ="DELETE from ROLE_PER where ROLEID = ?" ;						//删除这个人所有的权限
		try {
			 conn = Dbcp.getConnection();
			conn.setAutoCommit(false);
			Object[] count = runner.query(conn,sql_count,new ArrayHandler(),roleid);			//只有一行个数
			//System.out.println("查询到的所有的权限记录:"+count[0]);
			result = runner.update(conn,sql_del,roleid);			//删除这些记录
			//System.out.println("删除成功的个数："+result);
			if(String.valueOf(count[0]).equals(result+"")){			
				//System.out.println("删除成功!");
				conn.commit();//如果删除成功就提交事物事物否则回滚
			}else{
				conn.rollback(); 		//删除不成功回滚事物,并结束程序
				if(conn!=null&&conn.isClosed() == false){
					conn.close();
				}
				return -1 ;
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally{			//关闭连接释放支援
			try {
				if(conn!=null&&conn.isClosed() == false){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	

	
	public int updateRole(Role role) {
		int result = 0 ;
		QueryRunner runner = DaoFactory.getRunner();
		
		String sql = "UPDATE ROLE SET rolename =? , description=?, status = ?, remarks=? where roleid= ?" ;
		Object[] params = {role.getRolename(),role.getDesc(),role.getStatus(),role.getRemark(),role.getRoleid()};
		try {
			result  = runner.update(sql,params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}